﻿CREATE PROCEDURE [dbo].[Hogar_Actividad_ObtenerTodas]   
AS 
BEGIN

/*****************************************************************************************************************************************/
-- Author: Carlos Mendoza
-- Purpose:   obtener todas las actividades 
-- Returns:    Actividad
/*****************************************************************************************************************************************/ 
	SET LANGUAGE Spanish
	
	SELECT *
	FROM (SELECT a.[ActividadId], a.[Nombre], a.[TipoActividadId], CASE WHEN [Activa] = 1 THEN 1 ELSE 0 END AS [Activa], a.[ResponsableId], ISNULL(ag.[Apellido],'') + ' ' + ISNULL(ag.[Nombre],'') as [Responsable], 
				 DATENAME(DW, ha.[DiaSemanaId] - 1) as [DiaSemana], 
				 CASE WHEN HoraComienzo = '' THEN '' ELSE (HoraComienzo + '-' + HoraFin) END as [Horario]
		  FROM [dbo].[Actividad] a WITH (NOLOCK)
		  LEFT JOIN [dbo].[Agente] ag WITH (NOLOCK) ON a.[ResponsableId] = ag.[AgenteId]
		  INNER JOIN [dbo].[HorarioActividad] ha WITH (NOLOCK) ON a.[ActividadId] = ha.[ActividadId]) as ac
	PIVOT (
		Max(ac.[Horario])
		FOR [DiaSemana] IN ([Lunes],[Martes],[Miércoles],[Jueves],[Viernes])
	) p
	ORDER BY [Nombre]
	
END